from datetime import datetime

import pymysql
import hashlib


class DBAccessor:
    Connection = None
    Cursor = None

    def __init__(self):
        """
        初始化数据库，若暂未完成建库，则创建数据库以及相关表
        之后获取连接、游标
        """
        self.Connection = pymysql.connect(host='localhost', port=3306, user='root', password='123456',
                                          autocommit=False)  # 获取连接
        self.Cursor = self.Connection.cursor()  # 获取游标
        self.Cursor.execute("show databases")  # 查询数据库是否存在
        if ('magiccalendardb',) not in self.Cursor.fetchall():
            print("Creating database...")
            self.handle_init()
        else:
            self.Cursor.execute("use `MagicCalendarDB`;")

    def handle_init(self):
        # 建库
        self.Cursor.execute("CREATE DATABASE if not exists `MagicCalendarDB` CHARACTER SET 'utf8mb4';")  # 创建数据库
        self.Cursor.execute("use `MagicCalendarDB`;")
        # # 权限列表
        # self.Cursor.execute("\
        #     CREATE TABLE `Privileges`  (\
        #     `Level` tinyint UNSIGNED NOT NULL,\
        #     `Self_Update` tinyint(1) NOT NULL CHECK(Self_Update IN (0, 1)) DEFAULT(0),\
        #     `Self_Select` tinyint(1) NOT NULL CHECK(Self_Select IN (0, 1)) DEFAULT(0),\
        #     `Other_Update` tinyint(1) NOT NULL CHECK(Other_Update IN (0, 1)) DEFAULT(0),\
        #     `Other_Select` tinyint(1) NOT NULL CHECK(Other_Select IN (0, 1)) DEFAULT(0),\
        #     `Any_Update` tinyint(1) NOT NULL CHECK(Any_Update IN (0, 1)) DEFAULT(0),\
        #     `Any_Select` tinyint(1) NOT NULL CHECK(Any_Select IN (0, 1)) DEFAULT(0),\
        #     PRIMARY KEY (`Level`));")
        # # 生成权限等级最高与最低
        # self.Cursor.execute("INSERT INTO `Privileges` VALUES (0, 0, 0, 0, 0, 0, 0);")
        # self.Cursor.execute("INSERT INTO `Privileges` VALUES (255, 1, 1, 1, 1, 1, 1);")
        # 建用户表
        self.Cursor.execute("\
            CREATE TABLE `Users`  (\
            `Uid` mediumint(7) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,\
            `Nickname` varchar(32) UNIQUE,\
            `Password` varchar(32) NOT NULL,\
            PRIMARY KEY (`Uid`));")
        # FOREIGN KEY (`Privilege_Level`) REFERENCES `privileges` (`Level`) ON DELETE RESTRICT ON UPDATE CASCADE);
        # 重置自增长、设置背锅侠与超级管理员
        self.Cursor.execute("ALTER TABLE users AUTO_INCREMENT = 0;")
        self.Cursor.execute(
            "INSERT INTO users(Nickname, Password) VALUE ('Canceled_Account','5a97fd47c6e21034a9a2de9d689f2f2d');")
        self.Cursor.execute(
            "INSERT INTO users(Nickname, Password) VALUE ('Super_Admin','5a97fd47c6e21034a9a2de9d689f2f2d');")
        # self.Cursor.execute(
        #     "INSERT INTO users(Nickname, Password, PRIVILEGE_level) VALUE ('Canceled_Account','5a97fd47c6e21034a9a2de9d689f2f2d', 0);")
        # self.Cursor.execute(
        #     "INSERT INTO users(Nickname, Password, PRIVILEGE_level) VALUE ('Super_Admin','5a97fd47c6e21034a9a2de9d689f2f2d', 255);")
        # 建日程表
        self.Cursor.execute("\
            CREATE TABLE `Schedule`  (\
            `Sid` int UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,\
            `Sname` varchar(32),\
            `Start_Time` datetime NOT NULL,\
            `End_Time` datetime,\
            `Cycle_List` varchar(255),\
            PRIMARY KEY (`Sid`));")
        # 建用户-日程表
        self.Cursor.execute("\
            CREATE TABLE `US`  (\
            `Uid` mediumint(7) UNSIGNED ZEROFILL NOT NULL,\
            `Sid` int UNSIGNED ZEROFILL NOT NULL,\
            `Uid_Pub` mediumint(7) UNSIGNED ZEROFILL DEFAULT(1),\
            `Description` varchar(255) NULL,\
            PRIMARY KEY (`Uid`, `Sid`),\
            FOREIGN KEY (`Uid`) REFERENCES `users` (`Uid`) ON DELETE CASCADE ON UPDATE CASCADE,\
            FOREIGN KEY (`Sid`) REFERENCES `schedule` (`Sid`) ON DELETE CASCADE ON UPDATE CASCADE,\
            FOREIGN KEY (`Uid_Pub`) REFERENCES `users` (`Uid`) ON DELETE SET NULL ON UPDATE CASCADE);")
        # `Remind_Time` time NULL,\

    def init_view(self, uid: int):
        """
        创建视图
        """
        self.Cursor.execute("show tables")
        result = self.Cursor.fetchall()
        # 判断是否有视图
        for table in result:
            if table[0] == "view" + str(uid):
                return
        # 没有视图就添加视图
        sql = """CREATE VIEW `view""" + str(uid) + """` AS SELECT
                us.Uid,
                us.Sid,
                us.Uid_Pub,
                us.Description,
                SCHEDULE.Sname,
                SCHEDULE.Start_Time,
                SCHEDULE.End_Time,
                SCHEDULE.Cycle_List,
                users.Nickname 
            FROM
                users,
                SCHEDULE,
                us 
            WHERE
                us.Uid = %s 
                AND us.Sid = SCHEDULE.Sid 
                AND users.Uid = us.Uid;"""
        value = (uid)
        self.Cursor.execute(sql, value)
        self.Cursor.execute("commit;")

    def insert_user(self, nickname, password_md5) -> int:
        """
        用户表-插入
        :param nickname: 昵称
        :param password_md5: 密码md5值
        :return: 若插入成功，返回用户ID；否则返回-1
        """
        # 将用户信息插入数据库
        sql = "INSERT INTO users(Nickname, Password) VALUE (%s, %s);"
        value = (nickname, password_md5)
        try:
            self.Cursor.execute(sql, value)  # 执行SQL
        except Exception as e:
            print("Error occurred when inserting user...\n", e)
            self.Cursor.execute("rollback;")
            return -1  # 插入失败
        else:
            self.Cursor.execute("commit;")  # 提交事务

        # 查询用户ID
        sql = "SELECT Uid FROM users WHERE Nickname=%s;"
        value = (nickname,)
        self.Cursor.execute(sql, value)  # 执行SQL
        return self.Cursor.fetchall()[0][0]

    def select_user(self, uid: int = 0, nickname: str = '', password_md5: str = '') -> tuple:
        """
        用户表-查询登录
        :param uid: 账号
        :param password_md5: 密码md5值
        :return: 若查询成功，返回用户ID与昵称；否则返回-1
        """
        if password_md5 == '' and uid != 0:
            # 密码为空且uid不为空，查昵称
            sql = "SELECT Uid, Nickname FROM users WHERE Uid=%s;"
            value = (uid,)
        elif password_md5 == '' and nickname != '':
            # 密码为空且昵称不为空，查uid
            sql = "SELECT Uid, Nickname FROM users WHERE Nickname=%s;"
            value = (nickname,)
        else:
            # 密码不为空，查询登录
            sql = "SELECT Uid, Nickname FROM users WHERE Uid=%s AND Password=%s;"
            value = (uid, password_md5)
        self.Cursor.execute(sql, value)
        result = self.Cursor.fetchall()
        # print(result)
        if result == tuple():
            print("Can't find this user...")
            return ()
        else:
            if len(result) > 1:
                print("More than one user found...")
                return ()
            else:
                return result[0]

    def insert_schedule(self, sname, starttime, endtime, cycle, uid, uid_pub, description):
        """
        日程表-插入
        :param sname: 日程名
        :param starttime: 开始时间
        :param endtime: 结束时间
        :param cycle: 循环时间
        :param description: 描述
        """
        # 将日程信息插入数据库
        sql = "INSERT INTO schedule(Sname, Start_Time, End_Time, Cycle_List) VALUE (%s, %s, %s, %s);"
        value = (sname, starttime, endtime, cycle)
        try:
            self.Cursor.execute(sql, value)  # 执行SQL
        except Exception as e:
            print("Error occurred when inserting schedule...\n", e)
            self.Cursor.execute("rollback;")
            return
        else:
            self.Cursor.execute("commit;")

        # 查询日程ID
        self.Cursor.execute("SELECT @@IDENTITY;")
        sid = self.Cursor.fetchall()[0][0]

        sql2 = "INSERT INTO us(Uid, Sid, Uid_Pub, Description) VALUE (%s, %s, %s, %s);"
        value2 = (uid, sid, uid_pub, description)
        try:
            self.Cursor.execute(sql2, value2)  # 执行SQL
        except Exception as e:
            print("Error occurred when inserting us...\n", e)
            self.Cursor.execute("rollback;")
            return
        else:
            self.Cursor.execute("commit;")

    def delete_schedule(self, sid: int):
        """
        日程表-删除
        :param sid: 日程ID
        """
        sql = "DELETE FROM schedule WHERE Sid=%s;"
        value = (sid,)
        try:
            self.Cursor.execute(sql, value)  # 执行SQL
        except Exception as e:
            print("Error occurred when deleting schedule...\n", e)
            self.Cursor.execute("rollback;")
            return
        else:
            self.Cursor.execute("commit;")

    def select_schedule(self, uid: int, sid: int) -> tuple:
        """
        日程表-查询
        元组排列为：Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname
        :return: 查询到的日程
        """
        sql = "SELECT Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname FROM view%s WHERE Sid=%s;"
        value = (uid, sid)
        self.Cursor.execute(sql, value)
        result: tuple = self.Cursor.fetchall()
        return result[0]

    def update_schedule(self, sid, sname, starttime, endtime, cycle, description):
        # 修改schedule表
        sql = "UPDATE schedule SET Sname=%s, Start_Time=%s, End_Time=%s, Cycle_List=%s WHERE Sid=%s;"
        value = (sname, starttime, endtime, cycle, sid)
        try:
            self.Cursor.execute(sql, value)  # 执行SQL
        except Exception as e:
            print("Error occurred when updating schedule...\n", e)
            self.Cursor.execute("rollback;")
            return
        else:
            self.Cursor.execute("commit;")
        # 修改us表
        sql2 = "UPDATE us SET Description=%s WHERE Sid=%s;"
        value2 = (description, sid)
        try:
            self.Cursor.execute(sql2, value2)  # 执行SQL
        except Exception as e:
            print("Error occurred when updating us...\n", e)
            self.Cursor.execute("rollback;")
            return
        else:
            self.Cursor.execute("commit;")

    def get_all_schedules(self, uid) -> list:
        """
        获取所有日程
        元组排列为：Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname
        :return: 所有日程的列表，元组排列为
        """
        # sql = "SELECT * FROM 'view%s' WHERE us.Sid=schedule.Sid AND Uid=%s;"
        sql = "SELECT * FROM view%s;"
        value = (uid, uid)
        self.Cursor.execute(sql, value)
        result: tuple = self.Cursor.fetchall()
        return list(result)

    def get_before_schedules(self, uid) -> list:
        """
        获取所有日程
        元组排列为：Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname
        :return: 所有日程的列表，元组排列为
        """
        # sql = "SELECT * FROM 'view%s' WHERE us.Sid=schedule.Sid AND Uid=%s;"
        time = datetime.now()
        sql = "SELECT Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname FROM view%s WHERE END_TIME <= NOW() ORDER BY END_TIME ASC;"
        value = (uid)
        self.Cursor.execute(sql, value)
        result: tuple = self.Cursor.fetchall()
        return list(result)

    def get_after_schedules(self, uid) -> list:
        """
        获取所有日程
        元组排列为：Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname
        :return: 所有日程的列表，元组排列为
        """
        # sql = "SELECT * FROM 'view%s' WHERE us.Sid=schedule.Sid AND Uid=%s;"
        sql = "SELECT Uid,Sid,Uid_Pub,Description,Sname,Start_Time,End_Time,Cycle_List,Nickname FROM view%s WHERE END_TIME > NOW() ORDER BY END_TIME DESC;"
        value = (uid)
        self.Cursor.execute(sql, value)
        result: tuple = self.Cursor.fetchall()
        return list(result)


def encode_password(password: str) -> str:
    md = hashlib.md5(password.encode())  # 生成md5对象
    md5pwd = md.hexdigest()  # 获取加密后的字符串
    return md5pwd
